import { RowDataPacket } from 'mysql2';
import pool from '../config/database';

export interface IAudio extends RowDataPacket {
  id: number;
  title: string;
  description: string;
  file_path: string;
  duration: number;
  category_id: number;
  created_at: Date;
  updated_at: Date;
}

// 创建音频表
export const createAudioTable = async (): Promise<void> => {
  const createTableSQL = `
    CREATE TABLE IF NOT EXISTS audios (
      id INT PRIMARY KEY AUTO_INCREMENT,
      title VARCHAR(100) NOT NULL,
      description TEXT NOT NULL,
      file_path VARCHAR(255) NOT NULL,
      duration INT NOT NULL,
      category_id INT NOT NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      FOREIGN KEY (category_id) REFERENCES categories(id)
    )
  `;

  try {
    await pool.query(createTableSQL);
    console.log('Audios table created or already exists');
  } catch (error) {
    console.error('Error creating audios table:', error);
    throw error;
  }
};

// 音频相关操作
export const Audio = {
  // 创建音频
  async create(audioData: Omit<IAudio, 'id' | 'created_at' | 'updated_at'>): Promise<IAudio> {
    const [result] = await pool.query<IAudio[]>(
      'INSERT INTO audios (title, description, file_path, duration, category_id) VALUES (?, ?, ?, ?, ?)',
      [audioData.title, audioData.description, audioData.file_path, audioData.duration, audioData.category_id]
    );
    return result[0];
  },

  // 获取所有音频
  async findAll(): Promise<IAudio[]> {
    const [rows] = await pool.query<IAudio[]>(
      'SELECT * FROM audios'
    );
    return rows;
  },

  // 根据ID获取音频
  async findById(id: number): Promise<IAudio | null> {
    const [rows] = await pool.query<IAudio[]>(
      'SELECT * FROM audios WHERE id = ?',
      [id]
    );
    return rows[0] || null;
  },

  // 根据分类获取音频
  async findByCategory(categoryId: number): Promise<IAudio[]> {
    const [rows] = await pool.query<IAudio[]>(
      'SELECT * FROM audios WHERE category_id = ?',
      [categoryId]
    );
    return rows;
  },

  // 更新音频
  async update(id: number, data: Partial<IAudio>): Promise<boolean> {
    const [result] = await pool.query(
      'UPDATE audios SET ? WHERE id = ?',
      [data, id]
    );
    return (result as any).affectedRows > 0;
  },

  // 删除音频
  async delete(id: number): Promise<boolean> {
    const [result] = await pool.query(
      'DELETE FROM audios WHERE id = ?',
      [id]
    );
    return (result as any).affectedRows > 0;
  }
}; 